﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommunitySystem.Model;
using System.Data;
using CommunitySystemModel;
using System.Data.SqlClient;
using System.Diagnostics;

namespace CommunitySystem.BO 
{
    public class DictionaryBo
    {
        public DictionaryBo(string key)
        { 
            
        }

        public IList<DictionaryModel> GetSpouseByPID(string parentid)
        {
            IList<DictionaryModel> list = new List<DictionaryModel>();

            
            DataSet ds = new DataSet();
            try
            {
                ds = new AccessHelper().ExcuteDataSet(string.Format(@"select [Id],'' AS Value,'' AS ParentKey ,[DictName] as Name,[DictCode] as Code,[IsDynamic] as Type,[IsTree],[DynamicSQL] as SQL,0 AS [Sequence],'' AS [Key] from DataDictionary WHERE DICTCODE='{0}' Order by Dictname", parentid)); 
            }
            catch (Exception ex)
            {

            }
            if (ds.Tables[0].Rows[0]["Type"].ToString() == "False")
            {

                DataSet ds1 = new AccessHelper().ExcuteDataSet(string.Format(@"select di.Id ,'' as Value, di.DictValue as Name,di.DictCode as Code,[IsDynamic] as Type,[IsTree],
      [DynamicSQL] as SQL,'' as ParentKey,0 AS [Sequence],'' as [Key] from DataDictionary d
      inner join DataDictionaryItem di on d.Id = di.ParentId WHERE d.DICTCODE='{0}'
      Order by Dictname", parentid));

                foreach (DataRow dr in ds1.Tables[0].Rows)
                {
                    DictionaryModel model = new DictionaryModel();

                    model.Code = dr["Code"].ToString();
                    model.Id = int.Parse(dr["Id"].ToString());
                    model.IsTree = Convert.ToBoolean(dr["IsTree"].ToString());
                    model.Key = dr["Key"].ToString();
                    model.Name = dr["Name"].ToString();
                    model.ParentKey = dr["ParentKey"].ToString();
                    model.Sequence = int.Parse(dr["Sequence"].ToString());
                    model.SQL = dr["SQL"].ToString();
                    model.Type =0;
                    model.Value = dr["Value"].ToString();
                    //model.
                    list.Add(model);

                }
            }

            else
            {
                DataSet ds2 = new AccessHelper().ExcuteDataSet(ds.Tables[0].Rows[0]["SQL"].ToString());

                if (ds2.Tables[0].Rows.Count > 0)
                {
                    var listbysql = new List<DictionaryModel>();
                    foreach (DataRow r in ds2.Tables[0].Rows)
                    {
                        listbysql.Add(new DictionaryModel() { Name = r["DictValue"].ToString(), Code = r["DictCode"].ToString(), Id = Convert.ToInt32(r["Id"].ToString()) });
                    }
                    list = listbysql;
                }
            }
            
            return list;
        }

        public IList<DictionaryModel> GetDictionarys()
        {
            var listbysql = new List<DictionaryModel>();
            DataSet ds = new AccessHelper().ExcuteDataSet("select Id, DictName,DictCode,case IsDynamic when 'false' then '静态' when 'true' then '动态' end as Type from DataDictionary");
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                listbysql.Add(new DictionaryModel() { Name = r["DictName"].ToString(), Code = r["DictCode"].ToString(), Id = Convert.ToInt32(r["Id"].ToString()),  Value =r["Type"].ToString() });

            }
            return listbysql;
        }

        public DataDictionaryModel GetDataDictionaryModel(string DictCode, string parameter)
        {
            DataDictionaryModel model = new DataDictionaryModel();
            string sql = null;
            //if (string.IsNullOrEmpty(parameter))
            //    sql = string.Format(@"exec DictProceDure '{0}'", DictCode);
            //else
            //{
            //    sql = string.Format(@"exec DictProceDureWithParam '{0}', '{1}'", DictCode, parameter);
            //}
            //SqlConnection conn = new SqlConnection("server=192.168.15.157;database=ZhouPuCommunityDB;uid=sa;pwd=123456;");
            //conn.Open();
            ////SqlCommand cmmd = new SqlCommand(sql, conn);
            //SqlDataAdapter adpter = new SqlDataAdapter(sql, conn);
            //DataSet ds = new DataSet();
            //adpter.Fill(ds);
            SqlConnection conn = null;
            SqlTransaction transaction = AccessHelper.GetInstance().BeginTransaction(out conn);
            string DynamicSql = null;
            try
            {
                sql = string.Format(@"select IsTree, IsDynamic, Isnull(DynamicSQL, '') DynamicSQL from DataDictionary where DictCode='{0}'", DictCode);
                SqlDataReader reader = new SqlCommand(sql, conn, transaction).ExecuteReader();
                Debug.WriteLine("1: \t"+sql);
                reader.Read();
                bool Istree = (bool)reader[0];
                bool IsDynamic = (bool)reader[1];
                DynamicSql = reader[2].ToString();
                reader.Close();
                Debug.WriteLine("2: \t" + Istree.ToString() + IsDynamic.ToString() + DynamicSql);
                DataSet ds = new DataSet();
                if (IsDynamic && !string.IsNullOrEmpty(DynamicSql))
                {
                    if (!string.IsNullOrEmpty(parameter))//参数转换
                    {
                        IEnumerable<string> parameters = parameter.Split(';').Where(c=> c != "");
                        foreach (string p in parameters)
                        {
                            string[] KeyValue = p.Split('=');
                            string key = ":" + KeyValue[0];
                            string value = KeyValue[1];
                            DynamicSql = DynamicSql.Replace(key, value);
                        }
                    }
                    Debug.WriteLine("3: \t" + DynamicSql);
                    //DynamicSql += "     select "+ Istr
                    reader = new SqlCommand(DynamicSql, conn, transaction).ExecuteReader();
                    //adpeter.Fill(ds);
                }
                else if(!IsDynamic)
                {
                    DynamicSql = string.Format(@"select i.* from DataDictionary d
left join DataDictionaryItem i on i.ParentId = d.Id
where d.DictCode = '{0}'", DictCode);
                    //new SqlCommand().
                    //SqlDataAdapter adpeter = new SqlDataAdapter(DynamicSql, conn);
                    //adpeter.Fill(ds);
                    reader = new SqlCommand(DynamicSql, conn, transaction).ExecuteReader();
                }
                //DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                model.DictCode = DictCode;
                if (reader == null)
                    return null;
                while (reader.Read())
                {
                    DataDictionaryItemModel item = new DataDictionaryItemModel();
                    item.DictCode = reader["DictCode"].ToString();
                    item.DictValue = reader["DictValue"].ToString();
                    item.Id = (int)reader["Id"];
                    item.ParentId = (int)reader["ParentId"];
                    item.DictPinYin = reader["DictPinYin"].ToString();
                    model.Item.Add(item);
                }
                reader.Close();
                //foreach (DataRow dr in ds.Tables[0].Rows)
                //{
                //    //model.IsTree = (bool)dr["IsTree"];
                //    DataDictionaryItemModel item = new DataDictionaryItemModel();
                //    item.DictCode = dr["DictCode"].ToString();
                //    item.DictValue = dr["DictValue"].ToString();
                //    item.Id = (int)dr["id"];
                //    item.ParentId = (int)dr["ParentId"];
                //    item.DictPinYin = dr["DictPinYin"].ToString();
                //    model.Item.Add(item);
                //}
                model.IsTree = Istree;
                AccessHelper.GetInstance().EndTransaction(transaction, conn);
            }
            catch (Exception ex)
            {
                model.DictCode = DynamicSql;//ex.Message;//throw ex;
            }
            return model;
        }
    }
}
